// dao.js

const sqlite3 = require('sqlite3')
const Promise = require('bluebird')

class DAO {
  constructor(dbFilePath) {
    this.db = new sqlite3.Database(dbFilePath, (err) => {
      if (err) {
        console.log('Could not connect to database', err)
      } else {
        console.log('Connected to database')
      }
    })
  }

  run(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.run(sql, params, function (err) {
        if (err) {
          console.log('Error running sql ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve({ id: this.lastID })
        }
      })
    })
  }

  get(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.get(sql, params, (err, result) => {
        if (err) {
          console.log('Error running sql: ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve(result)
        }
      })
    })
  }

  all(sql, params = []) {
    return new Promise((resolve, reject) => {
      this.db.all(sql, params, (err, rows) => {
        if (err) {
          console.log('Error running sql: ' + sql)
          console.log(err)
          reject(err)
        } else {
          resolve(rows)
        }
      })
    })
  }

  insert(table, params = {}) {
    let { sql, array } = this._sqls_insert(table, params)
    return this.run(sql, array)
  }

  update(table, params = {}, update = {}) {
    let { sql, array } = this._sqls_update(table, params, update)
    return this.run(sql, array)
  }

  delete(table, where) {
    let params = []
    for (let key in where) {
      params.push(key + ' ' + where[key])
    }
    if (params.length == 0) return
    let sql = `DELETE FROM ` + table + ` WHERE 1 AND ` + params.join('AND')
    return this.run(sql)
  }

  find(table, where, order) {
    let sql = this._sql_where(where)
    if (sql === false) sql = ''
    if (order) order = ` ORDER BY ` + order
    else order = ''
    sql = `SELECT * FROM ` + table + ` WHERE 1 AND ` + sql + order
    return this.get(sql)
  }

  select(table, where, order) {
    let sql = this._sql_where(where)
    if (sql === false) sql = ''
    if (order) order = ` ORDER BY ` + order
    else order = ''
    sql = `SELECT * FROM ` + table + ` WHERE 1 AND ` + sql + order
    return this.all(sql)
  }

  _sqls_insert(table, params = {}) {
    let keys = [],
      values = [],
      array = []
    for (let key in params) {
      keys.push(key)
      array.push(params[key])
      values.push('?')
    }
    if (keys.length == 0) return
    let sql =
      `INSERT INTO ` +
      table +
      ` ( ` +
      keys.join(',') +
      `)
        VALUES (` +
      values.join(',') +
      `)`

    return { sql, array }
  }
  _sqls_update(table, params = {}, where) {
    let sets = [],
      array = []
    for (let key in params) {
      sets.push(key + ' = ?')
      array.push(params[key])
    }
    if (array.length == 0) return
    let whereSql = this._sql_where(where)
    if (whereSql === false) return

    let sql =
      `UPDATE ` + table + ` SET ` + sets.join(',') + ` WHERE 1 AND ` + whereSql

    return { sql, array }
  }
  _sql_where(where) {
    if (typeof where == 'string') {
      return where
    }
    let whereArr = []
    for (let key in where) {
      whereArr.push(key + ' ' + where[key])
    }
    if (whereArr.length == 0) return false

    let sql = whereArr.join(' AND ')

    return sql
  }
}
